﻿using Dapper;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using WinFormDapper.entry;

namespace WinFormDapper
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        //获取plan数据信息
        private void button1_Click(object sender, EventArgs e)
        {

            IDbConnection conn = new SqlConnection(AppData.Instance.connectString);
            //查询计划表当中包含的数据
            //暂时使用*代替 实际使用时 需要按照需要的信息进行查询 不允许有select * 的存在
            var t_Plans = conn.Query<t_plan>("SELECT * from t_plan");
            //lambda表达式
            // var temp = t_Plans.Where(d => d.plan_no == "2020091401").Select(d => d).ToList();
            dataGridView1.DataSource = t_Plans;
            LogSystemInfo.DebugInfo($"获取订单信息,获取的数量是{t_Plans.Count()}");
        }

        //获取detail数据信息
        private void button2_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new SqlConnection(AppData.Instance.connectString);

            var t_Plan_Details = conn.Query<t_plan_detail>("SELECT * from t_plan_detail");
            dataGridView2.DataSource = t_Plan_Details;
        }

        //两者信息同时获取
        private void button3_Click(object sender, EventArgs e)
        {

            button4_Click(null, null);
            IDbConnection conn = new SqlConnection(AppData.Instance.connectString);
            var sql = "select * from t_plan; select * from t_plan_detail";

            var multiReader = conn.QueryMultiple(sql);

            var planList = multiReader.Read<t_plan>();

            var detailList = multiReader.Read<t_plan_detail>();

            dataGridView1.DataSource = planList;
            dataGridView2.DataSource = detailList;

        }


        //更新plan_no时 也要同时更新plan_detial当中的数据
        private void button5_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new SqlConnection(AppData.Instance.connectString);
            try
            {
                string updateSql = "Update t_plan_detail SET plan_number = @plan_number  Where plan_no = @plan_no and plan_item = @plan_item";
                t_plan_detail plan_Detail = new t_plan_detail
                {
                    plan_no = tb_planno.Text,
                    plan_item = tb_plan_item.Text,
                    plan_number = int.Parse(tb_plan_num.Text)
                };
                var result = conn.Execute(updateSql, plan_Detail);
                if (result <= 0)
                {
                    MessageBox.Show("更新数据失败");
                }
                else
                {
                    MessageBox.Show("更新数据成功");
                    button3_Click(null, null);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("操作出错，请根据错误提示进行问题的排查" + ex.Message);

            }
            finally
            {
                conn.Close();
            }
        }


        //清除全部的信息
        private void button4_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = null;
            dataGridView2.DataSource = null;


        }

        private void dataGridView2_SelectionChanged(object sender, EventArgs e)
        {
            tb_planno.Text = dataGridView2.CurrentRow.Cells["plan_no"].Value.ToString();
            tb_plan_item.Text = dataGridView2.CurrentRow.Cells["plan_item"].Value.ToString();
        }

        private void dataGridView1_SelectionChanged(object sender, EventArgs e)
        {
            //tb_delete_planno.Text = dataGridView1.CurrentRow.Cells["plan_no"].Value.ToString();


        }

        private void button6_Click(object sender, EventArgs e)
        {
            string sql1 = $"delete from t_plan where plan_no = '{tb_delete_planno.Text}'";
            string sql2 = $"delete from t_plan_detail where plan_no='{tb_delete_planno.Text}'";
            using (IDbConnection conn = new SqlConnection(AppData.Instance.connectString))
            {
                conn.Open();
                IDbTransaction transaction = conn.BeginTransaction();
                try
                {
                    conn.Execute(sql1, null, transaction);
                    conn.Execute(sql2, null, transaction);
                    transaction.Commit();
                    MessageBox.Show("数据删除成功,请重新刷新数据进行显示");
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    MessageBox.Show(exception.Message);
                    LogSystemInfo.LogErrorInfo($"删除数据报错", exception);
                }
            }
        }

        //新增数据
        private void button7_Click(object sender, EventArgs e)
        {
            using (IDbConnection conn = new SqlConnection(AppData.Instance.connectString))
            {
                conn.Open();
                IDbTransaction transaction = conn.BeginTransaction();
                try
                {
                    t_plan t_Plan = new t_plan
                    {
                        plan_no = tb_new_plan_no.Text,
                        plan_date = DateTime.Now,
                        create_user_no = "test",
                        create_user_name = "test"
                    };
                    conn.Execute("Insert into t_plan (plan_no,plan_date,create_user_no,create_user_name) values (@plan_no,@plan_date,@create_user_no,@create_user_name)", t_Plan, transaction);
                    t_plan_detail t_Plan_Detail = new t_plan_detail
                    {
                        plan_no = tb_new_plan_no.Text,
                        plan_item = tb_item_no.Text,
                        plan_money = decimal.Parse(tb_product_money.Text),
                        plan_number = int.Parse(tb_product_num.Text)
                    };
                    conn.Execute("Insert into t_plan_detail (plan_no,plan_item,plan_number ,plan_money) values (@plan_no,@plan_item,@plan_number,@plan_money)", t_Plan_Detail, transaction);
                    transaction.Commit();
                    MessageBox.Show("数据插入成功,请重新刷新数据进行显示");
                }
                catch (Exception exception)
                {
                    transaction.Rollback();
                    MessageBox.Show(exception.Message);
                }
            }
        }

        private void button8_Click(object sender, EventArgs e)
        {
            //使用存储过程新增数据
            using (IDbConnection conn = new SqlConnection(AppData.Instance.connectString))
            {
                conn.Open();

                try
                {
                    var parems = new DynamicParameters();//建立一个parem对象
                    parems.Add("@plan_no", tb_plan_no_store.Text);
                    parems.Add("@plan_item_no", tb_item_no_store.Text);
                    parems.Add("@plan_item_number", int.Parse(tb_product_number_store.Text));
                    parems.Add("@plan_item_money", decimal.Parse(tb_money_store.Text));
                    parems.Add("@result", 0, DbType.Int32, ParameterDirection.Output);
                    parems.Add("@appCode", "", DbType.String, ParameterDirection.Output);
                    parems.Add("@errorMsg", "", DbType.String, ParameterDirection.Output);
                    conn.Execute("sp_insert_new_record", parems, commandType: CommandType.StoredProcedure);
                    int result = parems.Get<int>("@result");
                    string appCode = parems.Get<string>("@appCode");
                    string errorMsg = parems.Get<string>("@errorMsg");
                    MessageBox.Show($"存储过程新增数据成功,请重新刷新数据进行显示,返回的数据是:结果{result},code是{appCode},结果是{errorMsg}");
                }
                catch (Exception exception)
                {

                    MessageBox.Show(exception.Message);
                }
            }
        }
        private bool RunFlag = false;
        private bool ControlFlag = false;

        private void button9_Click(object sender, EventArgs e)
        {
            RunFlag = true;
            ControlFlag = true;
            var testThread = new Thread(new ThreadStart(() =>
              {
                  while (RunFlag)
                  {
                      if (ControlFlag)
                      {
                          //读取数据 并且进行显示
                          IDbConnection conn = new SqlConnection(AppData.Instance.connectString);
                          //查询计划表当中包含的数据
                          //暂时使用*代替 实际使用时 需要按照需要的信息进行查询 不允许有select * 的存在
                          var t_Plans = conn.Query<t_plan>("SELECT * from t_plan");
                          this.BeginInvoke(new Action(() =>
                          {
                              dataGridView1.DataSource = t_Plans;
                          }));
                          LogSystemInfo.DebugInfo($"获取订单信息,获取的数量是{t_Plans.Count()}");
                          Thread.Sleep(2000);
                      }
                      else
                      {
                          Thread.Sleep(100);
                          LogSystemInfo.DebugInfo($"ControlFlag为false,100ms执行一次log的保存");
                      }

                  }

              }));
            testThread.IsBackground = true;
            testThread.Start();
        }

        private void button10_Click(object sender, EventArgs e)
        {
            ControlFlag = false;
        }

        private void button11_Click(object sender, EventArgs e)
        {
            ControlFlag = true;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            RunFlag = false;
            Thread.Sleep(2001);
        }

        private void button12_Click(object sender, EventArgs e)
        {
            IDbConnection conn = new OracleConnection(AppData.Instance.OracleConnectString);
            conn.Open();
            var dt = conn.Query("SELECT x.*,x.ROWID FROM DEPT x").ToList();
            dataGridView1.DataSource = dt;

        }
    }
}
